package com.edu.realtime.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;

import com.alibaba.fastjson.JSONObject;
import com.edu.realtime.common.EduConfig;

/**
 * @author zcx
 * @create 2022-10-18 21:01 操作phoenix的工具类
 */
public class PhoenixUtil {
    // 执行SQL语句的方法
    public static void executeSql(String sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 从连接池获取连接对象
            conn = DruidDSUtil.getConnection();
            // 获取数据库操作对象
            ps = conn.prepareStatement(sql);
            // 执行SQL语句
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 执行SQL语句的方法
    public static void executeSqlUsePrepareStatement(String sinkTable, JSONObject jsonObj) {

        // 拼接upsert语句
        ArrayList<String> keys = new ArrayList<>();
        ArrayList<String> values = new ArrayList<>();
        Set<Map.Entry<String, Object>> entrySet = jsonObj.entrySet();
        for (Map.Entry<String, Object> entry : entrySet) {
            String key = entry.getKey();
            keys.add(key);
            Object value = entry.getValue();
            if (value != null) {
                values.add(value.toString());
            } else {
                values.add("");
            }
        }
        // 处理预编译占位符
        int size = keys.size();
        StringBuilder placeholder = new StringBuilder("");
        for (int i = 0; i < size; i++) {
            placeholder.append("?");
            if (i != size - 1) {
                placeholder.append(",");
            }
        }

        String upsertSql = "upsert into " + EduConfig.PHOENIX_SCHEMA + "." + sinkTable + "("
            + StringUtils.join(keys, ",") + ") " + " values(" + placeholder + ")";

        System.out.println("向phoenix表中插入数据的SQL：" + upsertSql);

        String upsertSql2 =
            "upsert into " + EduConfig.PHOENIX_SCHEMA + "." + sinkTable + "(" + StringUtils.join(jsonObj.keySet(), ",")
                + ") " + " values('" + StringUtils.join(jsonObj.values(), "','") + "')";
        System.out.println("upsertSql2 = " + upsertSql2);

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 从连接池获取连接对象
            conn = DruidDSUtil.getConnection();
            // 获取数据库操作对象
            ps = conn.prepareStatement(upsertSql);

            for (int i = 0; i < values.size(); i++) {
                ps.setString(i + 1, values.get(i));
            }

            // 执行SQL语句
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static <T> java.util.List<T> queryList(String sql, Class<T> clz) {
        List<T> resList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = DruidDSUtil.getConnection();
            // 获取数据库操作对象
            ps = conn.prepareStatement(sql);
            // 执行SQL语句
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()) {
                // 创建对象 用于封装每条查询结果
                T obj = clz.newInstance();
                // 获取表中的所有列
                for (int i = 1; i < metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    // 给对象的属性赋值
                    BeanUtils.setProperty(obj, columnName, columnValue);
                }
                // 将对象放到List集合中
                resList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return resList;
    }

    public static void main(String[] args) {
        System.out.println(queryList("select * from EDU_REALTIME.student", JSONObject.class));
    }
}
